package com.neeraj.SharingArtifacts;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	private static final String DEBUG_TAG = "DATABASE HELPER";
//	private static DatabaseHelper instance;
	
	static final String dbName="sharing_artifacts";
	//location table attributes
	static final String locationTable="locationTable";
	static final String loc_ID="loc_ID";
	static final String latitude="latitude";
	static final String longitude="longitude";
	static final String altitude="altitude";
	static final String utc_time="utc_time";
	static final String date="date";
	//artifact table attributes
	static final String artifactTable="artifactTable";
	static final String artifact_ID="artifact_ID";
	static final String locID="locID";
	static final String artifactTypeID="artifactTypeID";
	static final String content="content";
	static final String content_file_name="content_file_name";
	static final String isUpload="isUpload";	
	//type table attributes
	static final String typeTable="typeTable";
	static final String artifactType_ID="artifactType_ID";
	static final String artifactType="artifactType";
	
	
	private SQLiteDatabase _db;
	private double latitude_, longitude_;
	long locationTableInsertID, typeTableInsertID, artifactTableInsertID;
	

	public DatabaseHelper(Context context) {
		super(context, dbName, null, 1);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
//		instance = this;
		String locationTableQuery = "CREATE TABLE IF NOT EXISTS " +locationTable+" ("+
									loc_ID+" integer PRIMARY KEY AUTOINCREMENT, "+
									latitude+" real ," +
									longitude+" real ," +
									altitude+" real ," +
									utc_time+" integer , " +
									date+" TEXT)";		
		db.execSQL(locationTableQuery);
				  
		String typeTableQuery = "CREATE TABLE IF NOT EXISTS " +typeTable+" ("+
				artifactType_ID+" integer PRIMARY KEY AUTOINCREMENT, "+				
				artifactType+" TEXT)";		
		db.execSQL(typeTableQuery);
		
		String artifactTableQuery = "CREATE TABLE IF NOT EXISTS " +artifactTable+" ("+
				artifact_ID+" integer PRIMARY KEY AUTOINCREMENT, "+
				locID+" integer ," +
				artifactTypeID+" integer ," +
				content+" TEXT ," +
				content_file_name+" TEXT ," +
				isUpload+" integer)";		
		db.execSQL(artifactTableQuery);
				
		
		db.execSQL("CREATE TRIGGER fk_data_name_nameid " +
				" BEFORE INSERT "+
				" ON "+artifactTable+
				
				" FOR EACH ROW BEGIN"+
				" SELECT CASE WHEN ((SELECT "+loc_ID+" FROM "+locationTable+" WHERE "+loc_ID+"=new."+ locID +" ) IS NULL)"+
				" THEN RAISE (ABORT,'Foreign Key Violation') END;"+
				"  END;");
		
		db.execSQL("CREATE TRIGGER fk_data_place_placeid " +
				" BEFORE INSERT "+
				" ON "+artifactTable+
				
				" FOR EACH ROW BEGIN"+
				" SELECT CASE WHEN ((SELECT "+artifactType_ID+" FROM "+typeTable+" WHERE "+artifactType_ID+"=new."+ artifactTypeID +" ) IS NULL)"+
				" THEN RAISE (ABORT,'Foreign Key Violation') END;"+
				"  END;");		
	}
	
//	public static DatabaseHelper getInstance() {
//    	return instance;
//    }

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS "+locationTable);
		db.execSQL("DROP TABLE IF EXISTS "+typeTable);
		db.execSQL("DROP TABLE IF EXISTS "+artifactTable);		
	}
	
	public void insertData(Bundle b, int type) {
		_db = this.getWritableDatabase();
		ContentValues cv1 = new ContentValues();
		latitude_ = b.getDouble("latitude");
		longitude_ = b.getDouble("longitude");
		cv1.put(latitude, latitude_);
		cv1.put(longitude, longitude_);
		cv1.put(altitude, b.getDouble("altitude"));
		cv1.put(utc_time, b.getLong("utc_time"));
		cv1.put(date, b.getString("date"));
		locationTableInsertID = _db.insert(locationTable, latitude, cv1);
		Log.i(DEBUG_TAG, "insert in location table id:" + locationTableInsertID);
		
		ContentValues cv2 = new ContentValues();
		cv2.put(locID, getLocID());
		cv2.put(artifactTypeID, type);
		cv2.put(content, b.getString("content"));
		cv2.put(content_file_name, b.getString("content_file_name"));
		cv2.put(isUpload, b.getInt("isUpload"));
		artifactTableInsertID = _db.insert(artifactTable, content, cv2);
		Log.i(DEBUG_TAG, "insert in artifact table id:" + artifactTableInsertID);
		
		_db.close();
	}	
	
	public void insertTypeData() {
		_db = this.getWritableDatabase();				
				
		Cursor c = _db.rawQuery("Select " + artifactType_ID + " from "+ typeTable, null);
		int rowCount = c.getCount();		
		Log.i(DEBUG_TAG, "row: "+ rowCount);
		if(rowCount == 0) {
			ContentValues cv = new ContentValues();
			ArrayList<String> ar = new ArrayList<String>();
			ar.add("image");
			ar.add("audio");
			ar.add("video");
			ar.add("text");
			
			for (String string : ar) {
				cv.put(artifactType, string);
				_db.insert(typeTable, artifactType, cv);
			}
		}
		_db.close();
	}	
	
	public boolean isUploaded(String filename) {
		SQLiteDatabase db=this.getReadableDatabase();
		String [] param = new String[] {filename};
		String findUpload = "select "+isUpload+" from "+artifactTable+" where "+content_file_name+"=?";
		Cursor c1 = db.rawQuery(findUpload, param);
		boolean param1 = c1.moveToFirst();
		int param2 = c1.getInt(c1.getColumnIndex(isUpload));
		Log.i(DEBUG_TAG, "check upload updation"+param2);
		c1.close();
		db.close();
		if(param1 && (param2 == 1)) {
			return true;
		}
		else 
			return false;
	}
	
	int getLocID() {		
//		SQLiteDatabase db=this.getReadableDatabase();
		Log.i(DEBUG_TAG, String.valueOf(latitude_) +" , "+String.valueOf(longitude_));
		String [] params = new String[] {String.valueOf(latitude_), String.valueOf(longitude_)};
		Cursor c = _db.rawQuery("Select " + loc_ID + " from "+ locationTable + " where "+ latitude+ "=? and " +longitude+" =?", params);		
		c.moveToFirst();
		
		return c.getInt(c.getColumnIndex("loc_ID"));				
	}
	
	public ArrayList<String> getTextIDs(int id) {		
		Log.i(DEBUG_TAG, "Check for cursor!!");
		SQLiteDatabase db=this.getReadableDatabase();		
		String [] params = new String[] {String.valueOf(id), String.valueOf(0)};
		String query = "Select "+content_file_name+" from "+artifactTable+" where "+
				artifactTypeID+"=? and " +isUpload+" =?";
		
		Cursor c = db.rawQuery(query, params);		
		Log.i(DEBUG_TAG, "number rows: "+c.getCount());
				 	
		ArrayList<String> textIds = new ArrayList<String>();
    	if(c.moveToFirst()) {
    		do {
    			String textId = c.getString(c.getColumnIndex("content_file_name"));
    			textIds.add(textId);
    		}while(c.moveToNext());
    	}
    	c.close();
		db.close();
		return textIds;					
	}
	
	ArtifactAttributes[] getArtifacts(ArrayList<String> ar, Context con) {
		SQLiteDatabase db=this.getReadableDatabase();
		Log.i(DEBUG_TAG, "In getArtifacts():"+ar.toString());
		ArtifactAttributes[] art = new ArtifactAttributes[ar.size()];
		int i=0;
		for (String fileName : ar) {
			String [] param = new String[] {fileName};
				Log.i(DEBUG_TAG, "records related to file: "+fileName);
				String query = "SELECT "+latitude+", "+longitude+", "+altitude+", "+utc_time+", "+date+", "+content+", "+
						content_file_name+", "+artifactType+" FROM "+artifactTable+", "+locationTable+", "+typeTable+ 
						" WHERE "+artifactTable+"."+locID+"="+locationTable+"."+loc_ID +" and "+ artifactTable+"."+artifactTypeID + 
						"="+typeTable+"."+artifactType_ID +" and "+ content_file_name+"=?";
				Cursor c = db.rawQuery(query, param);
				Log.i(DEBUG_TAG, "Count of rows: "+c.getCount());
				if(c.moveToFirst()) {
					Log.i(DEBUG_TAG, c.toString());
					Log.i(DEBUG_TAG, "Columns: "+ c.getColumnCount());
					Log.i(DEBUG_TAG, ""+ c.getDouble(0)+","+c.getDouble(1)+","+c.getDouble(2)+","+c.getLong(3)+","+c.getString(4)+","+c.getString(5)+","+c.getString(6)+","+c.getString(7));
					art[i] = new ArtifactAttributes();
					art[i].setLatitude(c.getDouble(0));
					art[i].setLongitude(c.getDouble(1));
					art[i].setAltitude(c.getDouble(2));
					art[i].setUtc_time(c.getLong(3));
					art[i].setDate(c.getString(4));
					art[i].setContent(c.getString(5));
					art[i].setContent_file_name(c.getString(6));
					art[i].setArtifactType(c.getString(7));
				}
				else {
					Log.i(DEBUG_TAG, "Sorry in else No records retrieved");
				}
				c.close();
				Log.i(DEBUG_TAG, ""+art[i].getLatitude());
				Log.i(DEBUG_TAG, ""+art[i].getLongitude());
				Log.i(DEBUG_TAG, ""+art[i].getAltitude());
				Log.i(DEBUG_TAG, ""+art[i].getUtcTime());
				Log.i(DEBUG_TAG, ""+art[i].getDate());
				Log.i(DEBUG_TAG, ""+art[i].getContent());
				Log.i(DEBUG_TAG, ""+art[i].getContent_file_name());
				Log.i(DEBUG_TAG, ""+art[i].getArtifactType());
			++i;
		}
		db.close();
		Log.i(DEBUG_TAG, art.toString());
		return art;
	}
	
	public void updateIsUpload(String name) {
		Log.i(DEBUG_TAG, "content name : "+ name);
		SQLiteDatabase db=this.getWritableDatabase();				
		ContentValues cv=new ContentValues();
		
		cv.put("isUpload", 1);
//		db.update(artifactTable, cv, artifact_ID+"=?", new String []{String.valueOf(id)});
		int val = db.update(artifactTable, cv, content_file_name+"=?", new String []{name});
		
		Log.i(DEBUG_TAG, "update ID is : " + val);
		db.close();
	}	
}
